-- @owner: @zhanghuan96
-- @date: 2024-10-16
-- @testpoint: 使用if not exists创建schema，在模式中创建触发器
@conn PrimaryDbBmode;
--step1:使用if not exists创建schema，在模式中创建触发器 expect:成功
drop schema if exists test_schema_008;
create schema if not exists test_schema_008;
use test_schema_008;
create or replace function test_function_008() returns trigger as
    $$
    declare
    begin
        insert into test_table_des_008 values(new.c1,new.c2.new,c3);
        return new;
    end
    $$ language plpgsql;
/
--step2: expect:创建成功
drop table if exists test_table_scr_008;
create table test_table_scr_008(c1 int,c2 int,c3 int);
drop table if exists test_table_des_008;
create table test_table_des_008(c1 int,c2 int,c3 int);
drop trigger if exists test_trriger_008;
create trigger test_trriger_008
before insert on test_table_scr_008
for each row
execute procedure test_function_008();
/
--step3:使用if not exists创建同名schema，在模式中创建触发器 expect:notice提示schema已存在
create schema if not exists test_schema_008;
drop table if exists test_table_scr_008a;
create table test_table_scr_008a(c1 int,c2 int,c3 int);
drop table if exists test_table_des_008a;
create table test_table_des_008a(c1 int,c2 int,c3 int);
drop trigger if exists test_trriger_008a;
create trigger test_trriger_008a
before insert on test_table_scr_008a
for each row
execute procedure test_function_008();
/
--step4:清理环境 expect:成功
drop trigger if exists test_trriger_008;
drop trigger if exists test_trriger_008a;
drop function test_function_008()cascade;
drop schema test_schema_008 cascade;